package com.ssyt.hiring.company.service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang3.StringUtils;

import com.ssyt.hiring.common.PageModel;
import com.ssyt.hiring.common.util.DBUtil;
import com.ssyt.hiring.company.entity.ApplicationEntity;

public class ApplicationService {

	public PageModel<ApplicationEntity> list(ApplicationEntity application,
			int pageNo, int pageSize) {
		// List<ApplicationEntity> applicationList = new ArrayList<>();
		// List<Object> paramList = new ArrayList<Object>();
		// StringBuilder sb = new StringBuilder(
		// "SELECT a.id, p.name AS 'position$name', t.name AS 'talent$name', r.intention AS 'resume$intention', a.status, a.app_time, a.handle_time FROM application a, talent t, resume r, position p, company c WHERE a.talent_id=t.id AND a.resume_id=r.id AND a.position_id=p.id AND p.company_id=c.id ");
		// if (StringUtils.isNotBlank(application.getPosition$name())) {
		// sb.append("AND p.name LIKE ? ");
		// paramList.add("%" + application.getPosition$name() + "%");
		// }
		// if (application.getStatus() != 0) {
		// sb.append("AND a.status=? ");
		// paramList.add(application.getStatus());
		// }
		// if (StringUtils.isNotBlank(application.getAppTimeStart())) {
		// sb.append("AND a.app_time>=? ");
		// paramList.add(application.getAppTimeStart());
		// }
		// if (StringUtils.isNotBlank(application.getAppTimeEnd())) {
		// sb.append("AND a.app_time<=? ");
		// paramList.add(application.getAppTimeEnd());
		// }
		// sb.append("ORDER BY a.id DESC LIMIT ?, ?");
		//
		// paramList.add((pageNo - 1) * pageSize);
		// paramList.add(pageSize);
		// Connection connection = DBUtil.getConnection();
		// PreparedStatement preparedStatement = null;
		// ResultSet resultSet = null;
		// try {
		// preparedStatement = connection.prepareStatement(sb.toString());
		// DBUtil.setParameter(paramList, preparedStatement);
		// resultSet = preparedStatement.executeQuery();
		// while (resultSet.next()) {
		// ApplicationEntity applicationEntity = new ApplicationEntity();
		// applicationEntity.setId(resultSet.getInt("id"));
		// applicationEntity.setPosition$name(resultSet
		// .getString("position$name"));
		// applicationEntity.setTalent$name(resultSet
		// .getString("talent$name"));
		// applicationEntity.setResume$intention(resultSet
		// .getString("resume$intention"));
		// applicationEntity.setStatus(resultSet.getInt("status"));
		// applicationEntity
		// .setAppTime(resultSet.getTimestamp("app_time"));
		// applicationEntity.setHandleTime(resultSet
		// .getTimestamp("handle_time"));
		// applicationList.add(applicationEntity);
		// }
		// } catch (SQLException e) {
		// e.printStackTrace();
		// } finally {
		// DBUtil.close(resultSet, preparedStatement, connection);
		// }
		//
		// return new PageModel<ApplicationEntity>(applicationList,
		// getTotalRecords(application), pageNo, pageSize);

		List<Object> paramList = new ArrayList<Object>();
		StringBuilder sb = new StringBuilder(
				"SELECT a.id, p.name AS 'position$name', t.name AS 'talent$name', r.intention AS 'resume$intention', a.status, a.app_time, a.handle_time FROM application a, talent t, resume r, position p, company c WHERE a.talent_id=t.id AND a.resume_id=r.id AND a.position_id=p.id AND p.company_id=c.id ");
		if (StringUtils.isNotBlank(application.getPosition$name())) {
			sb.append("AND p.name LIKE ? ");
			paramList.add("%" + application.getPosition$name() + "%");
		}
		if (application.getStatus() != 0) {
			sb.append("AND a.status=? ");
			paramList.add(application.getStatus());
		}
		if (StringUtils.isNotBlank(application.getAppTimeStart())) {
			sb.append("AND a.app_time>=? ");
			paramList.add(application.getAppTimeStart());
		}
		if (StringUtils.isNotBlank(application.getAppTimeEnd())) {
			sb.append("AND a.app_time<=? ");
			paramList.add(application.getAppTimeEnd());
		}
		sb.append("ORDER BY a.id DESC LIMIT ?, ?");
		paramList.add((pageNo - 1) * pageSize);
		paramList.add(pageSize);

		return DBUtil.executeQueryByPage(ApplicationEntity.class, pageNo,
				pageSize, sb.toString(), paramList);
	}

	/**
	 * 查询总共有多少条记录
	 * 
	 * @param searchText
	 * @return
	 */
	private int getTotalRecords(ApplicationEntity application) {
		int totalRecords = 0;
		List<Object> paramList = new ArrayList<Object>();
		StringBuilder sb = new StringBuilder(
				"SELECT COUNT(*) AS 'totalRecords' FROM application a, talent t, resume r, position p, company c WHERE a.talent_id=t.id AND a.resume_id=r.id AND a.position_id=p.id AND p.company_id=c.id ");
		if (StringUtils.isNotBlank(application.getPosition$name())) {
			sb.append("AND p.name LIKE ? ");
			paramList.add("%" + application.getPosition$name() + "%");
		}
		if (application.getStatus() != 0) {
			sb.append("AND a.status=? ");
			paramList.add(application.getStatus());
		}
		if (StringUtils.isNotBlank(application.getAppTimeStart())) {
			sb.append("AND a.app_time>? ");
			paramList.add(application.getAppTimeStart());
		}
		if (StringUtils.isNotBlank(application.getAppTimeEnd())) {
			sb.append("AND a.app_time<? ");
			paramList.add(application.getAppTimeEnd());
		}
		Connection connection = DBUtil.getConnection();
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {
			preparedStatement = connection.prepareStatement(sb.toString());
			DBUtil.setParameter(paramList, preparedStatement);
			resultSet = preparedStatement.executeQuery();
			resultSet.next();
			totalRecords = resultSet.getInt("totalRecords");
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(resultSet, preparedStatement, connection);
		}
		return totalRecords;
	}

}
